In [1]:
import pymssql
import pyodbc
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import plotly 
from plotly import __version__
import  plotly.plotly as py
import plotly.tools as tls
import plotly.offline as offline
import plotly.graph_objs as go
import plotly.figure_factory as ff
from plotly.graph_objs import Surface
from mpl_toolkits.mplot3d import Axes3D
In [2]:
Prod_Rank_List =[]
Level_Id_List =[]
Date=""
rank=0
SetDateFlag = False
itertn=0

conn = pyodbc.connect('DRIVER={ODBC Driver 11 for SQL Server};SERVER=PRANAY-PC\SQLSERVER2014;DATABASE=FKWMS;UID=sa;PWD=SQL@2014')
conn.autocommit= True       
cursor = conn.cursor()  

def Level_Rank_Input():
    global level_rank
    print "Products in Warehouse are ranked between 1 to 11 as per their probablity of being ordered \n"
    print "Higher Demand of Product Item would imply Lower Ranking of the Item \n"
    print "Rank 0 corresponds to no product... It implies that slot is empty bin \n"
    level_rank = input("Enter the Level Rank for which Heat Map is to be Displayed : ")
    if(level_rank>6 or level_rank< 1 ):
        print "Leval Rank value should be between 1 and 6 "
        Level_Rank_Input()
    return level_rank

def OptionRearrangeBins():
    global option
    option = input("Please Enter Option 1 or 2 : ") 
    if(option>2 or option< 1 ):
        print "Option value should be 1 or 2 "
        OptionRearrangeBins()
    return option

def SP_HeatMap(option):
    global cursor,itertn,conn
    itertn+=1
    conn.autocommit= True
    NorecsUpdated = 0
    if(option==1):
        if(itertn>1):
            print "Rearranging by Total Number of Items Ordered : " + "\n"
        cursor.execute("exec [dbo].[USP_HeatMap_for_Quantity_V1_1] @order_date= '"+Date+"'")
        cursor.commit()      
        NorecsUpdated = cursor.execute("exec [dbo].[USP_HeatMap_Update_Test]").rowcount
        cursor.commit()
        print "NorecsUpdated : "+str(NorecsUpdated)+ "\n" 
        if(NorecsUpdated>0):  
                HeatMap_RankMatrix_perLevel(level_rank)
                SP_HeatMap(option)
        elif(NorecsUpdated<=0): 
                print "Already Updated....Further Rearrangement of Bins not Required  for Level "+ str(level_rank) +" for Order Date : "+Date+ "\n"
    elif(option==2): 
        if(itertn>1):
            print "Rearranging by Number of times an Order has been placed for an Item : " + "\n"
        cursor.execute("exec [dbo].[USP_HeatMap_for_Frequency_V1_1] @Order_Date= '"+Date+"'")
        cursor.commit()      
        NorecsUpdated = cursor.execute("exec [dbo].[USP_HeatMap_Update_Test]").rowcount
        cursor.commit()
        print "NorecsUpdated : "+str(NorecsUpdated)+ "\n"    
        if(NorecsUpdated>0):
                HeatMap_RankMatrix_perLevel(level_rank)
                SP_HeatMap(option)
        elif(NorecsUpdated<=0): 
                print "Already Updated....Further Rearrangement of Bins not Required  for Level "+ str(level_rank) +" for Order Date : "+Date+ "\n"
    
    

def RearrangeBinsLogic(option,level_rank):
    global cursor,conn,Date,SetDateFlag
    
    if option==1:
            print "Rearranging by Total Number of Items Ordered : " + "\n"
            if(SetDateFlag is False):
                 year =input("Enter Year \t") 
                 month =input("Enter Month \t")
                 day =input("Enter Date \t")
                 Date = str(year)+"-"+str(month)+"-"+str(day)            
            SetDateFlag = True
            SP_HeatMap(option)
    elif option==2:
            print "Rearranging by Number of times an Order has been placed for an Item : " + "\n"
            if(SetDateFlag is False):
                 year =input("Enter Year \t") 
                 month =input("Enter Month \t")
                 day =input("Enter Date \t")
                 Date = str(year)+"-"+str(month)+"-"+str(day)            
            SetDateFlag = True
            SP_HeatMap(option)

def HeatMap_RankMatrix_perLevel(level_rank):
    global cursor,Prod_Rank_List,Level_Id_list
    Prod_Rank_List =[]
    Level_Id_List = []
    cursor.execute("exec [dbo].[USP_HeatMap_RankMatrix_perLevel] @Level_Rank= '"+str(level_rank)+"'")
    rowsp = cursor.fetchone()
    while rowsp:
        if(rowsp[0] is not None):
            Prod_Rank_List.append(int(rowsp[0]))
            Level_Id_List.append(int(rowsp[1]))
        elif(rowsp[0] is None):
            Prod_Rank_List.append(0)
            Level_Id_List.append(int(rowsp[1]))
        rowsp = cursor.fetchone()

    cursor.commit()
    
    HeatMap_Display(Prod_Rank_List,Level_Id_List)
        
        
def HeatMap_Display(Prod_Rank_List,Level_Id_List) :
        WareHouse_LevelId_Array = np.array(Level_Id_List)
        WareHouse_Rank_Array = np.array(Prod_Rank_List)
        levelId_t =WareHouse_LevelId_Array.reshape(2320,1)
        ware_t = WareHouse_Rank_Array.reshape(2320,1)
        # ware_t = WareHouse_Rank_Array.reshape(80,1)
        Rank_Matrix = ware_t.reshape(58,40)
        # Rank_Matrix = ware_t.reshape(2,40)
        LevelID_Matrix =levelId_t.reshape(58,40)
        
        Rank_Matrix_Trnspsose =  Rank_Matrix.T
        Rank_Matrix_Trnspsose_Reverse = Rank_Matrix_Trnspsose[::-1]
        
        LevelID_Matrix_Transpose = LevelID_Matrix.T
        LevelID_Matrix_Transpose_Reverse = LevelID_Matrix_Transpose[::-1]
        
        x=['C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12','C13','C14','C15','C16','C17','C18','C19','C20','C21','C22','C23','C24','C25','C26','C27','C28','C29','C30','C31','C32','C33','C34','C35','C36','C37','C38','C39','C40','C41','C42','C43','C44','C45','C46','C47','C48','C49','C50','C51','C52','C53','C54','C55','C56','C57','C58']

        y=['B1','B2','B3','B4','B5','B6','B7','B8','B9','B10','B11','B12','B13','B14','B15','B16','B17','B18','B19','B20','B21','B22','B23','B24','B25','B26','B27','B28','B29','B30','B31','B32','B33','B34','B35','B36','B37','B38','B39','B40']

        
        %matplotlib inline
        plotly.offline.init_notebook_mode()  
        annotations = go.Annotations()
        hovertext = list()
        for n,row in enumerate(Rank_Matrix_Trnspsose):
            hovertext.append(list())
            for m, val in enumerate(row):
                    annotations.append(go.Annotation(text=str(LevelID_Matrix_Transpose[n][m]), x=x[m], y=y[n],
                                         xref='x1', yref='y1', showarrow=False))
                    if(Rank_Matrix_Trnspsose[n][m] != 0):
                        hovertext[-1].append('Column: {}<br />Band: {}<br />Prod Rank: {}<br />Level ID: {}'.format(x[m], y[n], Rank_Matrix_Trnspsose[n][m],str(LevelID_Matrix_Transpose[n][m])))
                    elif(Rank_Matrix_Trnspsose[n][m] == 0):
                        hovertext[-1].append('Column: {}<br />Band: {}<br />{}<br />Level ID: {}'.format(x[m], y[n],'Empty Level',str(LevelID_Matrix_Transpose[n][m])))

        height, width = Rank_Matrix_Trnspsose.shape                    
        #   trace = go.Heatmap(x=x, y=y, z=Rank_Matrix_Trnspsose, colorscale='Viridis', showscale=True,hoverinfo='text',text=hovertext) 
        trace = go.Heatmap(x=x, y=y, z=Rank_Matrix_Trnspsose, showscale=True,hoverinfo='text',text=hovertext) 
        fig = go.Figure(data=go.Data([trace]))
        fig['layout'].update(
             title="Annotated Heatmap",
             annotations=annotations,
             xaxis=go.XAxis(ticks='', side='top'),
             width=2500,
             height=1000,
             autosize=False
                   )
        plotly.offline.iplot(fig, filename='annotated_heatmap_text')        
        Prod_Rank_List =[]
        Level_Id_List =[]
        


class PythonDbConnect:
    def __init__(self, name):
        self.name = name

    def PrintRecordsFromDatabase(self):
        global level_rank,Prod_Rank_List,option,cursor,conn,rank
        # conn = pymssql.connect(host="PRANAY-PC\SQLSERVER2014", user='sa', password='SQL@2014', database='FKWMS')      
        print "HEAT MAP MATRIX : " +"\n"
        level_rank = Level_Rank_Input()
        print "level Rank is : ", level_rank
        HeatMap_RankMatrix_perLevel(level_rank)            
        print "How do you want to Rearrange the Bins in the WareHouse Shelves ? " + "\n"
        print "Based on 1) Total number of Items Ordered " + "\n"
        print "         2) Number of times an Order has been placed for an Item "+ "\n"
        option = OptionRearrangeBins()
      
        RearrangeBinsLogic(option,level_rank)
        
        
        
                  

if __name__ == "__main__":
    global SetDateFlag
    SetDateFlag = False
    dbObj = PythonDbConnect("Connect MS SQL")
    dbObj.PrintRecordsFromDatabase()
HEAT MAP MATRIX : 

Products in Warehouse are ranked between 1 to 11 as per their probablity of being ordered 

Higher Demand of Product Item would imply Lower Ranking of the Item 

Rank 0 corresponds to no product... It implies that slot is empty bin 

Enter the Level Rank for which Heat Map is to be Displayed : 5
level Rank is :  5
How do you want to Rearrange the Bins in the WareHouse Shelves ? 

Based on 1) Total number of Items Ordered 

         2) Number of times an Order has been placed for an Item 

Please Enter Option 1 or 2 : 2
Rearranging by Number of times an Order has been placed for an Item : 

Enter Year 	2017
Enter Month 	6
Enter Date 	1
NorecsUpdated : 88

Rearranging by Number of times an Order has been placed for an Item : 

NorecsUpdated : 36

Rearranging by Number of times an Order has been placed for an Item : 

NorecsUpdated : 15

Rearranging by Number of times an Order has been placed for an Item : 

NorecsUpdated : 6

Rearranging by Number of times an Order has been placed for an Item : 

NorecsUpdated : 2

Rearranging by Number of times an Order has been placed for an Item : 

NorecsUpdated : 0

Already Updated....Further Rearrangement of Bins not Required  for Level 5 for Order Date : 2017-6-1

In [ ]: